
Public Class CsvReader
    Private Shared fieldNr As Integer = 0
    Public Shared Function ImportToUDTDataSet(ByVal filepath As String, ByVal firstRowContainsHeader As Boolean, ByVal Owner As String) As DataSet
        Dim ds As New DataSet("UserDefinedTable")
        ds.Namespace = "DotNetNuke/UserDefinedTable"
        Dim dt As New DataTable("Data")
        Using myreader As New Microsoft.VisualBasic.FileIO.TextFieldParser(filepath)
            myreader.TextFieldType = FileIO.FieldType.Delimited
            myreader.SetDelimiters(";")
            Dim row As String()
            If Not myreader.EndOfData Then
                row = myreader.ReadFields

                For i As Integer = 1 To row.Length
                    Dim colName As String
                    If firstRowContainsHeader AndAlso row(i - 1) <> String.Empty Then
                        colName = row(i - 1)
                    Else
                        colName = "Column" + i.ToString()
                    End If
                    dt.Columns.Add(colName, GetType(System.String))
                Next

                Dim start As Boolean = True
                Do While Not myreader.EndOfData
                    If Not start Then
                        row = myreader.ReadFields
                    Else
                        start = False
                    End If
                    dt.LoadDataRow(row, System.Data.LoadOption.OverwriteChanges)
                Loop
                ds.Tables.Add(dt)
            End If
        End Using
        dt = New DataTable(DataSetTableName.Fields)
        dt.Columns.Add(FieldsTableColumn.Id, GetType(System.Int32))
        dt.Columns.Add(FieldsTableColumn.Title, GetType(System.String))
        dt.Columns.Add(FieldsTableColumn.Required, GetType(System.Boolean))
        dt.Columns.Add(FieldsTableColumn.Type, GetType(System.String))
        dt.Columns.Add(FieldsTableColumn.Default, GetType(System.String))
        dt.Columns.Add(FieldsTableColumn.Visible, GetType(System.Boolean))
        dt.Columns.Add(FieldsTableColumn.InputSettings, GetType(System.String))
        dt.Columns.Add(FieldsTableColumn.OutputSettings, GetType(System.String))
        dt.Columns.Add(FieldsTableColumn.Searchable, GetType(System.Boolean))
        dt.Columns.Add(FieldsTableColumn.HelpText, GetType(System.String))
        dt.Columns.Add(FieldsTableColumn.Order, GetType(System.Int32))
        dt.Columns.Add(FieldsTableColumn.IsPrivate, GetType(System.Boolean))
        dt.Columns.Add(FieldsTableColumn.NormalizeFlag, GetType(System.Boolean))
        dt.Columns.Add(FieldsTableColumn.ShowOnEdit, GetType(System.Boolean))
        dt.Columns.Add(FieldsTableColumn.ValidationMessage, GetType(System.String))
        dt.Columns.Add(FieldsTableColumn.ValidationRule, GetType(System.String))

        For Each dc As DataColumn In ds.Tables(DataSetTableName.Data).Columns
            AddUDTField(dt, dc.ColumnName, "String")
        Next
        ds.Tables.Add(dt)
        AddSpecialFields(ds, Owner, System.DateTime.Now())
        dt = New DataTable(DataSetTableName.Settings)
        dt.Columns.Add(SettingsTableColumn.Setting, GetType(System.String))
        dt.Columns.Add(SettingsTableColumn.Value, GetType(System.String))
        Dim dr As DataRow = dt.NewRow
        dr(SettingsTableColumn.Setting) = "UDT_Filter"
        dr(SettingsTableColumn.Value) = ""
        dt.Rows.Add(dr)
        ds.Tables.Add(dt)
        Return ds
    End Function


    Private Shared Sub AddUDTField(ByVal dt As DataTable, ByVal ColumnName As String, ByVal DataTypeName As String, Optional ByVal visible As Boolean = True)
        fieldNr += 1
        Dim dr As DataRow = dt.NewRow()
        dr(FieldsTableColumn.Id) = -1
        dr(FieldsTableColumn.Title) = ColumnName
        dr(FieldsTableColumn.Required) = False
        dr(FieldsTableColumn.Type) = DataTypeName
        dr(FieldsTableColumn.Default) = String.Empty
        dr(FieldsTableColumn.Visible) = visible
        dr(FieldsTableColumn.InputSettings) = String.Empty
        dr(FieldsTableColumn.OutputSettings) = String.Empty
        dr(FieldsTableColumn.Searchable) = False
        dr(FieldsTableColumn.HelpText) = String.Empty
        dr(FieldsTableColumn.Order) = fieldNr
        dr(FieldsTableColumn.IsPrivate) = False
        dr(FieldsTableColumn.NormalizeFlag) = False
        dr(FieldsTableColumn.ShowOnEdit) = True
        dr(FieldsTableColumn.ValidationMessage) = String.Empty
        dr(FieldsTableColumn.ValidationRule) = String.Empty


        dt.Rows.Add(dr)
    End Sub

    Private Shared Sub AddSpecialFields(ByVal ds As DataSet, ByVal user As String, ByVal now As System.DateTime)
        Dim FieldsTable As DataTable = ds.Tables(DataSetTableName.Fields)
        Dim CreatedByColumnName As String = String.Empty
        Dim CreatedAtColumnName As String = String.Empty
        Dim ChangedByColumnName As String = String.Empty
        Dim ChangedAtColumnName As String = String.Empty
        For Each dr As DataRow In FieldsTable.Rows
            If dr(FieldsTableColumn.Type) = "CreatedBy" Then CreatedByColumnName = dr(FieldsTableColumn.Title)
            If dr(FieldsTableColumn.Type) = "CreatedAt" Then CreatedAtColumnName = dr(FieldsTableColumn.Title)
            If dr(FieldsTableColumn.Type) = "ChangedBy" Then ChangedByColumnName = dr(FieldsTableColumn.Title)
            If dr(FieldsTableColumn.Type) = "ChangedAt" Then ChangedAtColumnName = dr(FieldsTableColumn.Title)
        Next
        If CreatedByColumnName = String.Empty Then AddUDTField(FieldsTable, "CreatedBy", "CreatedBy", False) : CreatedByColumnName = "CreatedBy"
        If CreatedAtColumnName = String.Empty Then AddUDTField(FieldsTable, "CreatedAt", "CreatedAt", False) : CreatedAtColumnName = "CreatedAt"
        If ChangedByColumnName = String.Empty Then AddUDTField(FieldsTable, "ChangedBy", "ChangedBy", False) : ChangedByColumnName = "ChangedBy"
        If ChangedAtColumnName = String.Empty Then AddUDTField(FieldsTable, "ChangedAt", "ChangedAt", False) : ChangedAtColumnName = "ChangedAt"

        Dim dt As DataTable = ds.Tables(DataSetTableName.Data)
        If Not dt.Columns.Contains(CreatedByColumnName) Then dt.Columns.Add(CreatedByColumnName, GetType(System.String))
        If Not dt.Columns.Contains(CreatedAtColumnName) Then dt.Columns.Add(CreatedAtColumnName, GetType(System.DateTime))
        If Not dt.Columns.Contains(ChangedByColumnName) Then dt.Columns.Add(ChangedByColumnName, GetType(System.String))
        If Not dt.Columns.Contains(ChangedAtColumnName) Then dt.Columns.Add(ChangedAtColumnName, GetType(System.DateTime))
        If Not dt.Columns.Contains(DataTableColumn.RowId) Then dt.Columns.Add(DataTableColumn.RowId, GetType(System.Int32))
        For Each dr As DataRow In dt.Rows
            If dr(CreatedByColumnName) Is DBNull.Value Then dr(CreatedByColumnName) = user
            If dr(CreatedAtColumnName) Is DBNull.Value Then dr(CreatedAtColumnName) = now
            If dr(ChangedByColumnName) Is DBNull.Value Then dr(ChangedByColumnName) = user
            If dr(ChangedAtColumnName) Is DBNull.Value Then dr(ChangedAtColumnName) = now
            If dr(DataTableColumn.RowId) Is DBNull.Value Then dr(DataTableColumn.RowId) = -1
        Next
    End Sub

End Class


